#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Mar 17 21:43:48 2023
通过指定的源安装
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pyspider
@author: jerry
"""
#读操作
import xlrd
#写操作
import xlwt


def get_table(worksheet,rowmax,colmax):
#通过坐标获取表格中的数据,按照先行后列格式,不能超出最大row和col,否则程序要出错
    table=[]
    row_begin=0
    while row_begin<rowmax:
        xinghao=worksheet.cell_value(row_begin,0)
        if xinghao=="型号":
            cell_value=[]#定义一个列表
            cell_value.append(worksheet.cell_value(row_begin+1,1))
            cell_value.append(worksheet.cell_value(row_begin+2,1))
            cell_value.append(worksheet.cell_value(row_begin+4,3))
            cell_value.append(worksheet.cell_value(row_begin+5,3))
            cell_value.append(worksheet.cell_value(row_begin+6,3))
            cell_value.append(worksheet.cell_value(row_begin+7,3))
            cell_value.append(worksheet.cell_value(row_begin+8,3))
            cell_value.append(worksheet.cell_value(row_begin+9,3))
            cell_value.append(worksheet.cell_value(row_begin+10,3))
            cell_value.append(worksheet.cell_value(row_begin+11,3))
            table.append(cell_value)
            row_begin+=14
            # for ixc in cell_value:
            #     print(ixc)
                
        else:
            row_begin+=1
    return table

def open_xls(filename):
#打开文件，获取excel文件的工作簿对象
    workbook=xlrd.open_workbook(filename)
    result=[]
    #获取所有sheet的名字
    names=workbook.sheet_names()
    # print(names)
    # worksheet=workbook.sheet_by_index(0)
    # print(worksheet)
    for isheet in names:
        worksheet=workbook.sheet_by_name(isheet)
        print(worksheet)
        #获取总行数
        row_max=worksheet.nrows
        #获取总列数
        col_max=worksheet.ncols
        table1=get_table(worksheet,row_max,col_max)
        result.extend(table1)
    return result

workbook=xlwt.Workbook(encoding="utf-8")
worksheet=workbook.add_sheet('test01')
title=[0,50,100,150,200,250,300,450]
for ic in range(0,len(title)):
    worksheet.write(0,ic+2,title[ic]) 
xls_table=open_xls("das.xls")
row=1
for icell in xls_table:
    col=0
    for ivalue in icell:
        worksheet.write(row,col,ivalue)
        col+=1
    row+=1
workbook.save('data.xls')